package org.srtc.infopub.datacenter.storage;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map.Entry;
import java.util.Set;

import org.srtc.infopub.tools.ProtocolPaser;
import org.srtc.infopub.tools.TestXML;


public class TestDB {

	private Connection conn;
	
	/**
	 * 快表，用户查叶子信息
	 */
	private TestQuitTable tqt;
	
	private TestCoalTable tct;
	
	private ProtocolPaser pp;
	
	
	public TestDB() {
		pp = new ProtocolPaser();
	}
	
	
	public void initDBConnection(String url, String user, String pwd) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, pwd);
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 产生创建信息类的表
	 * @return
	 */
	public String createSql(TestXML tXML) {
		tXML.baseInfo();
		String tableName = tXML.getNodeByTag(tXML.rootElement(), "code").getTextContent();
		String sql = "create table if not exists `" + tableName + "` (";
		Set<Entry<String, String>> set = tXML.getInfo().entrySet();
		Iterator<Entry<String, String>> it = set.iterator();
		
		while (it.hasNext()) {
			Entry<String, String> entry = it.next();
			String key = entry.getKey();
			String dataType = entry.getValue();
			
			sql = sql + "`" + key + "` " + dataType + ", ";
		}
		sql = sql + "`CoalInfo` varchar(32), `Time` varchar(32))";

		return sql;
	}
	

	/**
	 * 创建单个信息类数据表
	 * @param filename 信息类文件
	 */
	public void createDB(String filename) {
		TestXML tXML = new TestXML(filename);
		String sql = createSql(tXML);
		try {
			Statement stat = conn.createStatement();
			stat.executeUpdate(sql);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 创建所有信息类数据表，每一类一个表
	 * @param dirPath 信息类xml文件所在目录
	 * @param coalXML 矿表XML
	 */
	public void createAllDB(String dirPath, String coalXML) {
		tqt = new TestQuitTable();
		tqt.createQuitTable(dirPath);       //创建快表
		
		tct = new TestCoalTable(coalXML);   
		tct.coalInfos();                    //建立矿表
		
		File f = new File(dirPath);
		
		if (f.isDirectory()) {
			String[] files = f.list();
			for (int i = 0; i<files.length; i++) {
				createDB(dirPath + "/" + files[i]);
			}
		}
	}
	
	
	/**
	 * 得到query所要查找的信息类，即找到要查的数据库表
	 * @param query
	 * @return
	 */
	public String getInfoTableName(String query) {
		if (query == null) {
			return null;
		}
		String[] strs = query.split("\\.");
		if (strs.length == 0) {
			return null;
		}
		return strs[0];
	}
	
	
	/**
	 * 得到该叶子在数据库中所对应值
	 * @param leaf 所有叶子
	 * @param coalName 矿的名字
	 * @param rest 结果放在这个结构中
	 */
	public void getQueryRes(ArrayList<String> leaf, String coalName, HashMap<String, Object> rest) {
		
		String sql = "select `";
		String infoTable = getInfoTableName(leaf.get(0));

		for (int j = 0; j<leaf.size(); j++) {							
			sql = sql + leaf.get(j) + "`, `";
		}
		sql = sql.substring(0, sql.lastIndexOf(","));//去最后一个逗号
		sql = sql + " from `" + infoTable + "` where CoalInfo=" + "'" + coalName + "'";
		
		try {
			Statement stat = conn.createStatement();
			ResultSet res = stat.executeQuery(sql);
			
			while (res.next()) {
				for (int j = 0; j<leaf.size(); j++) {
					rest.put(leaf.get(j), res.getObject(j + 1));
				}
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 一条query的请求结果
	 * @param qu
	 * @param singleMap 结果放在该集合中
	 */
	public void singleQueryRes(String qu, HashMap<String, Object> singleMap) {
		
		String coalName = pp.getQueryField(qu, "coal");
		String exCoal = pp.getQueryField(qu, "exceptCoal");
		
		if (exCoal.equals("")) {
			ArrayList<String> infos = tct.getCoals().get(coalName);
			for (String info : infos) {
				ArrayList<String> leaf = tqt.quitTable.get(info + ".");
//				System.out.println(info);
//				System.out.println(leaf);
				getQueryRes(leaf, coalName, singleMap);	
			}
		}
		else {
			ArrayList<String> leaf = tqt.quitTable.get(exCoal);
//			System.out.println(exCoal);
			getQueryRes(leaf, coalName, singleMap);
		}
	}
	
	
	/**
	 * select查询得到的结果
	 * @param querys 用户query集
	 * @return
	 */
	public HashMap<String, Object> selectRest(ArrayList<String> querys) {
		HashMap<String, Object> restSet = new HashMap<String, Object>();
		
		for (int i = 0; i<querys.size(); i++) {
			singleQueryRes(querys.get(i), restSet);			
		}
		return restSet;
	}
	
	
	//////////////////////////////////////////////////有问题，得到类型太麻烦了！
	public void insert(HashMap<String, Object> map) {
		String sql = "insert into `";
		String infoTable = null;
		String coalInfo = null;
		boolean flag = false;
		
		Iterator<Entry<String, Object>> it = map.entrySet().iterator();
		while (it.hasNext()) {
			Entry<String, Object> en = it.next();			
			if (!flag) {
				coalInfo = pp.getQueryField("PROTOCOL." + en.getKey(), "coal");
				infoTable = getInfoTableName(pp.getQueryField("PROTOCOL." + en.getKey(), "exceptCoal"));
				sql = sql + infoTable + "` (";
				flag = true;
			}
			sql = sql + "`" + pp.getQueryField("PROTOCOL." + en.getKey(), "exceptCoal") + "`, ";			
		}
		sql = sql + "`CoalInfo`, `Time`) values (";
		
		HashMap<String, String> typeMap = getDescribeQueryRes(infoTable);       // get the map of leafName:type
		
		Iterator<Entry<String, Object>> it2 = map.entrySet().iterator();
		while (it2.hasNext()) {
			String append = null;
			Entry<String, Object> en = it2.next();	
			
			String type = typeMap.get(pp.getQueryField("PROTOCOL." + en.getKey(), "exceptCoal"));
//			System.out.println(en.getKey());
			if (type.equals("decimal(10,2)") || type.equals("int(8)")) {
				append = en.getValue() + "";
			}
			if (type.equals("varchar(32)")) {
				append = "'" + en.getValue() + "'";
			}
			sql = sql + append + ", ";					
		}
		sql = sql + "'" + coalInfo + "', '13:10:00')";
		
		System.out.println(sql);
		
		try {
			Statement stat = conn.createStatement();
			stat.executeUpdate(sql);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 根据一次desc查询结果得到名称与类型的map
	 * @param tableName
	 * @return
	 */
	public HashMap<String, String> getDescribeQueryRes (String tableName) {
		HashMap<String, String> sm = new HashMap<String, String>();
		
		try {
			Statement stat = conn.createStatement();
			String sql = "describe `" + tableName + "`";
			ResultSet set = stat.executeQuery(sql);
			
			while (set.next()) {
				sm.put((String)set.getObject(1), (String)set.getObject(2));
//				System.out.println(set.getString(1) + ": " + set.getString(2));
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		
		return sm;
	}
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		TestDB tDB = new TestDB();
		tDB.initDBConnection("jdbc:mysql://127.0.0.1:3306/yl_tt", "root", "root");
		tDB.createAllDB("xml", "1.xml");
		ArrayList<String> qs = new ArrayList<String>();
		qs.add("PROTOCOL.002.002.006.");
//		qs.add("PROTOCOL.002.");
		qs.add("PROTOCOL.002.002.004.");
		
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("001.001.001.", 1);
		map.put("001.001.002.", 1);
		map.put("001.001.003.", 1);
		map.put("001.001.004.", 1);
		map.put("001.001.005.", 1);
		map.put("001.001.006.001.", 1);
		map.put("001.001.006.002.", 1);
		map.put("001.001.007.", 1);
		
		tDB.insert(map);
		
//		tDB.getDescribeQueryRes("003");
		
		System.out.println(tDB.selectRest(qs));
	}

}
